IF OBJECT_ID('dbo.RptCSCRAgreementsReceivedbyType') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.RptCSCRAgreementsReceivedbyType
    IF OBJECT_ID('dbo.RptCSCRAgreementsReceivedbyType') IS NOT NULL
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.RptCSCRAgreementsReceivedbyType >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.RptCSCRAgreementsReceivedbyType >>>'
END
go
-- =======================================================================
-- Procedure Name: dbo.RptCSCRAgreementsReceivedbyType
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/5/2007
--
-- Description: Source of Report, "CSCRAgreementsReceivedbyType"
--                  In Insight DB Reporting Services
--
-- Provides data needed to ensure an equitable distribution of work 
-- cross agreement associates.
--
-- Parameters:  
--             @StartDate          Created Date
--             @EndDate            
--             @DomainUserId
--
--
-- Returns:     INT, 0=Success, -1=Error
--
-- SampleCall:
-- EXECUTE dbo.RptCSCRAgreementsReceivedbyType()
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/5/07     KC        Created
-- 10/18/07    KC        Fixed Searching on Dates with TimeComponent
-- 02/27/08    KC        Removed Security per Partners Request
-- 03/26/08    KC        No real change, but verified buckets.
-- 04/08/08    KC        Added CSCRManagerId to ResultSet for Drilldown
-- 04/14/08    KC        Using new View for Amendments and Masters
-- 04/16/08    KC        Changed CreateDate to PendingDate
-- 05/13/08    KC        Added SubAmend Deliverables to Search. Grouped
--                       Other and Misc. Added @IncludeDanaFarber
-- 05/20/08    KC        Fixed DanaFarber flag. not working properly
-- 05/21/08    KC        Converted to use Function (DanaFarber)
-- 05/22/08    KC        DFCPP changed to DFPCC, Added CSCR - Subcontract
--                       to Search Deliverables.
--                       Fixed Counting by Unioning Views.
-- 06/17/08    KC        Bug with DFPCC, Bucket had to be changed as well
--                       from (DFCPP). Also added 'Unassigned' for CaseMgr
-- 06/25/08    KC        Changed criteria for 'Clinical Research' 
-- 07/01/08    KC        Change to 'Clinical Research' Logic again.
--
-- =======================================================================
CREATE PROCEDURE dbo.RptCSCRAgreementsReceivedbyType
     (
     @StartDate                         DATETIME,  
     @EndDate                           DATETIME,
     @IncludeDanaFarber                 VARCHAR(3) = NULL,
     @DomainUserId                      INT
     ) 
                                   
AS
BEGIN
     -- Initial Settings
     SET NOCOUNT ON
     
     -- Declare Local Variables
     DECLARE   @Error         INT,
               @RowCount      INT,
               @Institutions  VARCHAR(50)
               
     -- Check DanaFarber
     IF UPPER(@IncludeDanaFarber) != 'YES'
          SET @IncludeDanaFarber = NULL

     -- Set a list of Institutions
     IF @IncludeDanaFarber IS NOT NULL
          SET  @Institutions = 'BWH,MGH,NWH,SRH,DFCI,DFPCC'
     ELSE
          SET  @Institutions = 'BWH,MGH,NWH,SRH'

     -- Have to get a list of all Agreements by CSCRCaseManager, Institution, and 
     -- SubActivity Type, then we'll slice the counts up when we return.
     CREATE TABLE #tt_Counts
          (CSCRManagerId                INT,
          CSCRCaseManagerName           VARCHAR(50) NOT NULL,
          Institution                   VARCHAR(50) NOT NULL,
          ActivityDescription           VARCHAR(128) NOT NULL,
          SubActivityType               VARCHAR(50) NULL,
          AgreementCount                INT NOT NULL)
               
     -- Get Raw Counts..
     INSERT    #tt_Counts
          (CSCRManagerId,
          CSCRCaseManagerName,
          Institution,
          ActivityDescription,
          SubActivityType,
          AgreementCount)
     SELECT    ISNULL(AI.CSCRManagerId,-1),
               ISNULL(AI.CSCRCaseManagerName,'Unassigned'),
               AI.Institution,
               AI.ActivityDescription,
               CASE WHEN AI.SubActivityType LIKE 'Trial%' THEN 'Trial'
                    WHEN AI.SubActivityType LIKE 'Support%' THEN 'Support'
                    WHEN AI.SubActivityType LIKE 'Consulting%' THEN 'Consulting'
                    --WHEN AI.SubActivityType LIKE 'Other%' OR
                    --     (AI.ActivityDescription = 'Other' AND AI.SubActivitytype = 'Clinical Services') THEN 'Other'
                    WHEN AI.SubActivityType LIKE 'CDA%' THEN 'CDA'                   
                    WHEN AI.SubActivityType LIKE 'MTA%' THEN 'MTA'
                    WHEN AI.InstrumentType = 'GIFT' AND AI.SubActivityType = NULL AND AI.SponsorTypeName = 'Industry' THEN 'Gift'
                    ELSE 'MISC' --AI.SubActivityType
               END,
               COUNT(AI.FolderNumber)
     FROM      dbo.AgrAgreement_Info AI INNER JOIN

               -- Master Proposals/Amendments and DELIVERABLES are picked up here...date is checked below
               (-- Deliverables
               SELECT    AI.FolderNumber AS MasterFolderNumber,
                         D.prop_no AS PropNumber,
                         CAST(ReportId AS VARCHAR(20)) AS FolderNumber,
                         ReportName AS ShortTitle,
                         REPORTCAT AS TranType,
                         MIN(ProcessedDate) AS PendingDate
               FROM      dbo.vwInfoEdCSCRDeliverables D
                         INNER JOIN dbo.[AgrAgreement_Info] AI ON D.[prop_no] = AI.[InfoEdPropNumber]
               WHERE     REPORTCAT_CODE IN ('REPORTCAT_36','REPORTCAT_34') AND     -- 'CSCR - Sub-Amendment' OR 'CSCR - Subcontract'
                         REPORTSTAT_CODE = 'REPORTSTAT_61'       -- 'Pending'
               GROUP BY  AI.FolderNumber,
                         D.prop_no,
                         ReportId,
                         ReportName,
                         REPORTCAT
               UNION
               -- Master and Amendments
               SELECT    MA.MasterFolderNumber,
                         MA.prop_no,
                         MA.inst_no,
                         NULL,
                         MA.TranType,
                         PendingDate
               FROM      dbo.vwInfoEdMasterAmendments MA ) PROPDEL ON AI.FolderNumber = PROPDEL.MasterFolderNumber

     WHERE     -- This is the Where clause from the Parent Report...
               (dbo.fnRemoveTime(PROPDEL.PendingDate) BETWEEN  @StartDate AND @EndDate) AND

               -- Certain Criteria to count: Definition of 'Clinical Research'
               -- Defn #1
               ((RTRIM(AI.ActivityDescription) = 'Clinical Research' AND 
               RTRIM(AI.SponsorTypeName) = 'Industry' AND
               RTRIM(AI.InstrumentType) = 'Contract' AND
               RIGHT(RTRIM(AI.SubActivityType ),3) = '(C)') 
               OR
               -- Defn #2
               (RTRIM(AI.ActivityDescription) = 'Clinical Research' AND 
               RTRIM(AI.SponsorTypeName) = 'Industry' AND
               RTRIM(AI.InstrumentType) = 'Gift' AND
               RTRIM(AI.SubActivityType) IN ('',NULL)) )          AND
               
               (AI.Institution IN (SELECT Code FROM dbo.fnGetMultiCriteriaCode(@Institutions)))
               
     GROUP BY  ISNULL(AI.CSCRManagerId,-1),
               ISNULL(AI.CSCRCaseManagerName,'Unassigned'),
               AI.Institution,
               AI.ActivityDescription,
               AI.SubActivityType,
               AI.InstrumentType,
               AI.SponsorTypeName

     SELECT @Error=@@ERROR,@RowCount=@@ROWCOUNT
     IF @Error!=0
     BEGIN
          DROP TABLE #tt_Counts
          RETURN -1
     END
     
     -- Result Set
     -- TotalCounts
     SELECT    TOT.CSCRManagerId,
               TOT.CSCRCaseManagerName,
               TOT.TotalCount,
               INST.BWHCount,
               INST.MGHCount,
               INST.NWHCount,
               INST.SRHCount,
               INST.DFCICount,
               SUB.TrialCount,
               SUB.SupportCount,
               SUB.ConsultingCount,
               SUB.OtherCount,
               SUB.CDACount,
               SUB.MTACount,
               SUB.GiftCount,
               SUB.MISCCount
     FROM
     (SELECT   TC1.CSCRManagerId,
               TC1.CSCRCaseManagerName,
               SUM(TC1.AgreementCount) AS TotalCount
     FROM      #tt_Counts TC1
     GROUP BY  TC1.CSCRManagerId,TC1.CSCRCaseManagerName) TOT 
     INNER JOIN 
               (-- InstitutionCounts
               SELECT    CSCRCaseManagerName,
                         ISNULL(MAX(BWHCount),0) AS BWHCount,
                         ISNULL(MAX(MGHCount),0) AS MGHCount,
                         ISNULL(MAX(NWHCount),0) AS NWHCount,
                         ISNULL(MAX(SRHCount),0) AS SRHCount,
                         ISNULL(MAX(DFCICount),0) AS DFCICount
               FROM
               (SELECT   CSCRCaseManagerName,
                         CASE WHEN Institution = 'BWH' THEN SUM(InstitutionCount)
                         END AS BWHCount,
                         CASE WHEN Institution = 'MGH' THEN SUM(InstitutionCount)
                         END AS MGHCount,
                         CASE WHEN Institution = 'NWH' THEN SUM(InstitutionCount)
                         END AS NWHCount,
                         CASE WHEN Institution = 'SRH' THEN SUM(InstitutionCount)
                         END AS SRHCount,
                         CASE WHEN Institution IN ('DFCI','DFPCC') THEN SUM(InstitutionCount)
                         END AS DFCICount
               FROM
               (SELECT   TC2.CSCRCaseManagerName,
                         TC2.Institution,
                         SUM(TC2.AgreementCount) AS InstitutionCount
               FROM      #tt_Counts TC2
               GROUP BY TC2.CSCRCaseManagerName, TC2.Institution) InstCnt1
               GROUP BY CSCRCaseManagerName, Institution) InstCnt2
               GROUP BY CSCRCaseManagerName) INST ON TOT.CSCRCaseManagerName = INST.CSCRCaseManagerName
     INNER JOIN
               (-- SubActivityTypeCounts
               SELECT    CSCRCaseManagerName,
                         ISNULL(MAX(TrialCount),0) AS TrialCount,
                         ISNULL(MAX(SupportCount),0) AS SupportCount,
                         ISNULL(MAX(ConsultingCount),0) AS ConsultingCount,
                         ISNULL(MAX(OtherCount),0) AS OtherCount,
                         ISNULL(MAX(CDACount),0) AS CDACount,
                         ISNULL(MAX(MTACount),0) AS MTACount,
                         ISNULL(MAX(GiftCount),0) AS GiftCount,
                         ISNULL(MAX(MISCCount),0) AS MISCCount
               FROM
               (SELECT   CSCRCaseManagerName,
                         CASE WHEN SubActivityType = 'Trial' THEN SUM(SubActivityTypeCount)
                         END AS TrialCount,
                         CASE WHEN SubActivityType = 'Support' THEN SUM(SubActivityTypeCount)
                         END AS SupportCount,
                         CASE WHEN SubActivityType = 'Consulting' THEN SUM(SubActivityTypeCount)
                         END AS ConsultingCount,
                         CASE WHEN SubActivityType = 'Other' THEN SUM(SubActivityTypeCount)
                         END AS OtherCount,
                         CASE WHEN SubActivityType = 'CDA' THEN SUM(SubActivityTypeCount)
                         END AS CDACount,
                         CASE WHEN SubActivityType = 'MTA' THEN SUM(SubActivityTypeCount)
                         END AS MTACount,
                         CASE WHEN SubActivityType = 'Gift' THEN SUM(SubActivityTypeCount)
                         END AS GiftCount,
                         CASE WHEN SubActivityType = 'MISC' THEN SUM(SubActivityTypeCount)
                         END AS MISCCount               
               FROM
               (SELECT   TC3.CSCRCaseManagerName,
                         TC3.SubActivityType,
                         SUM(TC3.AgreementCount) AS SubActivityTypeCount
               FROM      #tt_Counts TC3
               GROUP BY TC3.CSCRCaseManagerName, TC3.SubActivityType) SubCnt1
               GROUP BY CSCRCaseManagerName, SubActivityType) SubCnt2
               GROUP BY CSCRCaseManagerName ) SUB ON TOT.CSCRCaseManagerName = SUB.CSCRCaseManagerName

     
     -- Housecleaning
     DROP TABLE #tt_Counts
     
     
     -- Return Success
     RETURN 0

     
END
go

IF OBJECT_ID('dbo.RptCSCRAgreementsReceivedbyType') IS NOT NULL
    PRINT '<<< CREATED PROCEDURE dbo.RptCSCRAgreementsReceivedbyType >>>'
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.RptCSCRAgreementsReceivedbyType >>>'
go

GRANT EXECUTE ON dbo.RptCSCRAgreementsReceivedbyType TO InsightReport,Insight
go

